![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Multiblock ReadsWhen performing table scans, Oracle has the ability to read more than one block at a time, thus speeding up I/O. By reading more than one block at a time, Oracle reads a larger block from the disk and eliminates some disk seeks. By reducing disk seeks and reading larger blocks, both I/O and CPU overhead are reduced. This feature is called multiblock reads. Multiblock reads are beneficial but take advantage of only contiguous blocks. Blocks in an extent are always contiguous. If your data is in many small extents, the effect of multiblock reads is reduced. The amount of data read in a multiblock read is specified by the Oracle initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT. The value for this parameter should always be set high because there is rarely any disadvantage in doing so. The size of the I/Os depends on both DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE. To take advantage of multiblock reads, you should try to configure your system so that the database blocks are as contiguous as possible. To do this, you should try to create your database with optimally sized extents. Creating these extents may not be a straightforward process, however. By creating extents too large, Oracle may have a difficult time finding enough contiguous space to create these extents. On the other hand, creating extents too small not only adversely affects multiblock reads, it also causes more dynamic extensions. Knowing what your initial data and growth patterns will be may help in sizing your extents. Multiblock WritesNew in Oracle 7.3 is the multiblock writes feature. Multiblock writes are similar to multiblock reads and have many of the same requirements. Under certain conditions, you can perform multiblock writes:
Parallel Query OptionThe Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. These functions are queries, index creation, data loading, and recovery. In each of these functions, the general principle is the same: keep the processing going while Oracle is waiting for I/O. For most queries, the time spent waiting for the data to be retrieved from disk usually overshadows the amount of time actually spent processing the results. With the Parallel Query option, you can compensate for this by using several server processes to execute the query. While one process is waiting for I/Os to complete, other processes can be executing. If you are running on a Symmetric Multiprocessor (SMP) computer, a cluster, or an Massively Parallel Processing (MPP) machine, you can take maximum advantage of the Parallel Query option. Many processes working together can simultaneously process a single SQL statement, a situation known as parallel query processing. The other functions are known as parallel index creation, parallel loading, and parallel recovery, each of which is discussed in the following sections. Parallel Query ProcessingParallel Query Processing allows certain Oracle statements to be run in parallel by multiple server processes. The Oracle server can process the following statements in parallel:
Parallel queries are effective on large operations such as table scans and sorts.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |